#!/usr/bin/env python3
# -*- coding: utf-8 -*-

import time
import openpyxl
from common.path_setting import ensure_path_sep
from common.replace_load import replace_load
from config import config
from openpyxl.styles import Font, PatternFill, colors
from common.get_logger import GetLogger

log = GetLogger.get_logger()


class ReadExcel:
    """
    操作excel文件
    """

    def __init__(self, filename):
        # 获取测试用例excel工作簿
        self.filepath = ensure_path_sep("/data/" + filename)
        try:
            self.wk = openpyxl.load_workbook(filename=self.filepath)
            self.sheet_names = self.wk.sheetnames
        except:
            raise FileNotFoundError("找不到excel文件或文件已经被打开，请先关闭文件")

    def get_cases_data(self):
        """获取excel工作簿中工作表的数据"""
        case_names = self.get_case_name()
        values = []
        # 循环每个工作表
        for sheet_name in case_names:
            # 获取每一个工作表某个区间内的数据
            value = self.get_start_end_col_value(sheet_name)
            case_data = replace_load({"case_name": sheet_name, "step_datas": value})
            # case_data ={"case_name": sheet_name, "step_datas": value}
            values.append(case_data)
        return values

    # 获取执行用例的工作表名称
    def get_case_name(self):
        sheet = self.wk[config.cases_manager]
        case_names = []
        for row in range(2, sheet.max_row + 1):
            # 循环用例管理表每一行数据
            is_run = sheet.cell(row, config.cases_is_run_col).value
            language = sheet.cell(row, config.cases_language_col).value
            priority = sheet.cell(row, config.cases_priority_col).value
            name = sheet.cell(row, config.cases_case_name_col).value
            log.info(
                f"Path:{self.filepath} CaseName - {name}, Is Run - {is_run}, "
                f"Language - {language}, priority - {priority}")
            # 如果配置文件中的语系为“all”或和用例管理表中的语系一致，或者用例管理表中的语系为空，则执行用例
            if config.language in ["all", "All", "ALL"] or language == config.language or language is None:
                # 如果is_run为y且优先级小于等于配置文件中的优先级，则执行用例
                if is_run in ["y", "Y"] and (priority is None or priority <= config.testCasePriority):
                    case_name = sheet.cell(row, config.cases_worksheet_col).value
                    case_names.append(case_name)
        return case_names

    def get_start_end_col_value(self, sheet_name):
        # 获取工作表
        sheet = self.wk[sheet_name]
        values = []
        # 循环工作表的每一行
        for row in range(2, sheet.max_row + 1):
            step_data = []
            for col in range(config.excel_start, config.excel_end + 1):
                value = sheet.cell(row, col).value
                if value is not None:
                    step_data.append(value)
            # 检查该行数据是否为空，如果不为空再添加到values中
            if step_data:
                values.append(step_data)
        return values

    def write_step_result(self, sheet_name, row, col, result):
        case_sheet = self.wk[sheet_name]
        case_sheet.cell(row, col).value = result
        # 颜色填充  绿色通过，红色失败
        red_fail = PatternFill("solid", fgColor="00FF0000")
        green_pass = PatternFill("solid", fgColor="0000FF00")
        if result == "fail":
            case_sheet.cell(row, col).fill = red_fail
        elif result == "pass":
            case_sheet.cell(row, col).fill = green_pass
        else:
            log.warning("测试结果异常，写入测试结果失败")
        self.wk.save(self.filepath)

    # 把测试结果写入测试管理文件中
    def write_case_result(self, sheet_name=config.cases_manager, color_col=config.cases_result_col,
                          time_col=config.cases_time_col):
        # 获取执行用例的测试结果的数据
        # 颜色填充  绿色通过，红色失败
        red_fail = PatternFill("solid", fgColor="00FF0000")
        green_pass = PatternFill("solid", fgColor="0000FF00")
        skip_test = PatternFill("solid", fgColor="FFFFFFFF")
        sheet = self.wk[sheet_name]

        # 清空结果列的内容
        clear_column = config.cases_result_col
        for row in range(2, sheet.max_row + 1):
            sheet.cell(row, clear_column).value = None
            # 设置单元格填充样式为无色
            sheet.cell(row, clear_column).fill = PatternFill(fill_type=None)

        for row in range(2, sheet.max_row + 1):
            # 循环用例管理文件的每一行
            is_run = sheet.cell(row, config.cases_is_run_col).value
            language = sheet.cell(row, config.cases_language_col).value
            priority = sheet.cell(row, config.cases_priority_col).value
            # 如果配置文件中的语系为“all”或和用例管理表中的语系一致，或者用例管理表中的语系为空，则执行用例
            if config.language == "all" or language == config.language or language is None:
                # 如果用例需要执行，则写入执行时间，并获取该用例所有测试步骤的结果
                if is_run == "y" and (priority is None or priority <= config.testCasePriority):
                    # 写入执行时间
                    now_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
                    sheet.cell(row, time_col).value = now_time
                    case_name = sheet.cell(row, config.cases_worksheet_col).value
                    # 获取该用例所有测试步骤的结果
                    step_results = self.get_sheet_col_values(case_name)
                    if "fail" in step_results:
                        sheet.cell(row, color_col).value = "fail"
                        sheet.cell(row, color_col).fill = red_fail
                    else:
                        sheet.cell(row, color_col).value = "pass"
                        sheet.cell(row, color_col).fill = green_pass
                else:
                    # sheet.cell(row, time_col, value=None)
                    # sheet.cell(row, color_col, value=None)
                    sheet.cell(row, time_col).value = None
                    sheet.cell(row, color_col).value = None
                    sheet.cell(row, color_col).fill = skip_test
        self.wk.save(self.filepath)

    def get_sheet_col_values(self, sheet_name):
        """获取某个工作表某列的所有的数据"""
        values = []
        sheet = self.wk[sheet_name]
        for row in range(2, sheet.max_row + 1):
            sheet_result = sheet.cell(row, config.case_result).value
            values.append(sheet_result)
        return values

    # 写入测试步骤的时间
    def write_step_time(self, sheet_name, row, col=config.case_time):
        sheet = self.wk[sheet_name]
        now_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        # 创建一个字体对象，并设置颜色为黑色
        font = Font(color="000000")
        # 应用字体到单元格
        cell = sheet.cell(row=row, column=col)
        cell.value = now_time
        cell.font = font
        # 保存工作簿
        self.wk.save(self.filepath)
